import sqlite3
DbName = "limis_devices"
class Cdb:
    def __init__(self,dbName):
        self.dbName = dbName
        self.conn = None
        self.cursor = None
        self.__connect()
    def __connect(self):
        try:
            self.conn = sqlite3.connect(self.dbName)
            self.cursor = self.conn.cursor()
        except:
            print("conn db err!")
    def exec_query(self,sql,*parms):
        try:
            self.cursor.execute(sql,parms)
            values = self.cursor.fetchall()
        except:
            print("exec_query error,sql is=",sql)
            return None
        return values
    def exec_cmd(self,sql,*parms):
        try:
            self.cursor.execute(sql,parms)
            self.conn.commit()
        except:
            print("exec_cmd error,sql is=", sql)
    def close_connect(self):
        try:
            self.cursor.close()
            self.conn.close()
        except:
            print("close db err!")
def initDb():
    cdb = Cdb(DbName)
    sql = "create table if not exists limis_devices (id integer primary key autoincrement not null," \
          " device_type_text varchar(50), " \
          " device_name_text varchar(50), " \
          " device_serial_port_text varchar(50), " \
          " device_serial_bps_text varchar(50), " \
          "device_remark_text varchar(500))"
    cdb.exec_cmd(sql)
    cdb.close_connect()
def deleteAccount(id):
    cdb = Cdb(DbName)
    sql1 = "delete from limis_devices where id=?"
    cdb.exec_cmd(sql1,id)
    cdb.close_connect()
def addAccountInfo(device_type_text, device_name_text, device_serial_port_text,device_serial_bps_text, device_remark_text):
    cdb = Cdb(DbName)
    sql1 = "insert into limis_devices (device_type_text,  device_name_text, device_serial_port_text,device_serial_bps_text, device_remark_text) values (?,?,?,?,?)"
    cdb.exec_cmd(sql1, device_type_text,  device_name_text, device_serial_port_text,device_serial_bps_text, device_remark_text)
    sql2 = "select max(id) from limis_devices"
    res = cdb.exec_query(sql2)
    cdb.close_connect()
    return res[0][0]
def editAccountInfo(id,device_type_text, device_name_text, device_serial_port_text,device_serial_bps_text, device_remark_text):
    cdb = Cdb(DbName)
    sql1 = "update limis_devices set device_type_text=?,device_name_text=?,device_serial_port_text=?,device_serial_bps_text=?,device_remark_text=? where id=?"
    cdb.exec_cmd(sql1, device_type_text, device_name_text, device_serial_port_text,device_serial_bps_text, device_remark_text, id)
    cdb.close_connect()
def getData():
    cdb = Cdb(DbName)
    sql2 = "select * from limis_devices"
    res = cdb.exec_query(sql2)
    cdb.close_connect()
    return res
def getDataByName(name):
    cdb = Cdb(DbName)
    sql2 = "select * from limis_devices where device_name_text=?"
    res = cdb.exec_query(sql2,name)
    cdb.close_connect()
    return res